﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ELS.User;
using System.Windows.Forms;

namespace ELS.ReportStatistic
{
    class ReportStatisticDataAccess
    {
        string connectionString = UserCommon.connectionString;
        internal DataTable SelectAllDataReport()
        {
            DataTable dt = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT Employee.e_ID,Salary.s_ID,Employee.e_Family, Employee.e_Name,
                                                        Salary.s_Date, Salary.s_Salary
                                                        FROM Employee INNER JOIN Salary ON Employee.e_ID = Salary.s_eID 
                                                        order by e_ID", sqlConnection);                    
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }


        internal int UpdateRows(DataTable dtChanges)
        {
            int rowUpdate = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    for (int i = 0; i < dtChanges.Rows.Count; i++)
                    {
                        sqlComm.CommandText = @"UPDATE Employee SET   
                                                                Employee.e_Family = @e_Family,
                                                                Employee.e_Name = @e_Name                                                                
                                                WHERE 
                                                                Employee.e_ID = @e_ID;
                                                UPDATE Salary SET   
                                                                Salary.s_Date = @s_Date,
                                                                Salary.s_Salary = @s_Salary                                                                
                                                WHERE 
                                                                Salary.s_ID = @s_ID;                 
                                              ";
                        sqlComm.Parameters.Add("@e_Family", SqlDbType.NVarChar).Value = dtChanges.Rows[i]["e_Family"].ToString();
                        sqlComm.Parameters.Add("@e_Name", SqlDbType.NVarChar).Value = dtChanges.Rows[i]["e_Name"].ToString();
                        sqlComm.Parameters.Add("@e_ID", SqlDbType.Int).Value = dtChanges.Rows[i]["e_ID"];
                        sqlComm.Parameters.Add("@s_Date", SqlDbType.NVarChar).Value = dtChanges.Rows[i]["s_Date"].ToString();
                        sqlComm.Parameters.Add("@s_Salary", SqlDbType.NVarChar).Value = dtChanges.Rows[i]["s_Salary"].ToString();
                        sqlComm.Parameters.Add("@s_ID", SqlDbType.Int).Value = dtChanges.Rows[i]["s_ID"];
                        rowUpdate += sqlComm.ExecuteNonQuery();                        
                    }                    
                    
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return rowUpdate;
        }
    }
}
